from libraryms import db
from datetime import datetime
# 普通用户表
class normalusr(db.Model):
    nid=db.Column(db.Integer,primary_key=True,nullable=False,autoincrement=True)
    username=db.Column(db.String(30))
    password=db.Column(db.String(30))

# 管理员表
class adminusr(db.Model):
    aid=db.Column(db.Integer,primary_key=True,nullable=False,autoincrement=True)
    username=db.Column(db.String(30))
    password=db.Column(db.String(30))

# 用户信息表
class usrinfo(db.Model):
    uid=db.Column(db.Integer,primary_key=True,nullable=False,autoincrement=True)
    username=db.Column(db.String(30))
    tel=db.Column(db.String(30))
    sex=db.Column(db.String(30))
    intro=db.Column(db.String(300))

# 用户想法表
class usridea(db.Model):
    id=db.Column(db.Integer,primary_key=True,nullable=False,autoincrement=True)
    username = db.Column(db.String(30))
    text = db.Column(db.String(300))

# 图书有关表----------------------------------------------------
# 图书信息表
class bookitem(db.Model):
    bid=db.Column(db.Integer,primary_key=True,nullable=False,autoincrement=True)
    name=db.Column(db.String(50))
    author=db.Column(db.String(50))
    publish=db.Column(db.String(40))
    isbn=db.Column(db.String(60))
    price=db.Column(db.String(20))
    number=db.Column(db.Integer)
    intro=db.Column(db.String(3000))
    pubdate=db.Column(db.String(30))
    type=db.Column(db.String(30))

class bookCollect(db.Model):
    id = db.Column(db.Integer, primary_key=True, nullable=False, autoincrement=True)
    username = db.Column(db.String(30))
    isbn = db.Column(db.String(60))

class bookBorrow(db.Model):
    id = db.Column(db.Integer, primary_key=True, nullable=False, autoincrement=True)
    name = db.Column(db.String(50))
    borrowusr = db.Column(db.String(30))
    borrownum = db.Column(db.Integer)
    borrowdate=db.Column(db.String(30))
    shouldreturndate=db.Column(db.String(30))
    ischecking = db.Column(db.Integer)
    returnnum = db.Column(db.Integer)

class bookBorrowHistory(db.Model):
    id = db.Column(db.Integer, primary_key=True, nullable=False, autoincrement=True)
    name = db.Column(db.String(50))
    borrowusr = db.Column(db.String(30))
    borrowdate=db.Column(db.String(30))
    returndate=db.Column(db.String(30))

# 图书违约记录表
class bookDefaultRecord(db.Model):
    id = db.Column(db.Integer, primary_key=True, nullable=False, autoincrement=True)
    name = db.Column(db.String(50))
    borrowusr = db.Column(db.String(30))
    borrowdate = db.Column(db.String(30))
    shouldreturndate = db.Column(db.String(30))
    number = db.Column(db.Integer)
    borrowtimestamp = db.Column(db.String(50))
    returntimestamp = db.Column(db.String(50))
    ispayfine = db.Column(db.Integer)
    isreturnbook = db.Column(db.Integer)

# 新建图书信息表
class booknewitem(db.Model):
    bid=db.Column(db.Integer,primary_key=True,nullable=False,autoincrement=True)
    name=db.Column(db.String(50))
    author=db.Column(db.String(50))
    publish=db.Column(db.String(40))
    isbn=db.Column(db.String(60))
    price=db.Column(db.String(20))
    number=db.Column(db.Integer)
    intro=db.Column(db.String(3000))
    pubdate=db.Column(db.String(30))
    type = db.Column(db.String(30))

# 留言板评论表
class messageboardparentcomment(db.Model):
    id=db.Column(db.Integer,primary_key=True,nullable=False,autoincrement=True)
    fromId = db.Column(db.String(30))
    content = db.Column(db.String(300))
    likeNum = db.Column(db.Integer)
    createTime = db.Column(db.String(50))
    settop = db.Column(db.Integer)

class messageboardchildcomment(db.Model):
    id=db.Column(db.Integer,primary_key=True,nullable=False,autoincrement=True)
    commentId = db.Column(db.Integer)
    fromId = db.Column(db.String(30))
    content = db.Column(db.String(300))
    createTime = db.Column(db.String(50))

class Playlist(db.Model):
    __tablename__ = 'data2'  # 直接使用现有表名
    # 假设 name 是唯一标识符（需确保数据中 name 唯一）
    name = db.Column(db.String(255), primary_key=True)
    created_date = db.Column(db.String(255))
    play_count = db.Column(db.BigInteger)
    favorite_count = db.Column(db.String(255))
    share_count = db.Column(db.Integer)
    comment_count = db.Column(db.Integer)
    playlist_length = db.Column(db.Integer)
    tags = db.Column(db.String(255))
    type = db.Column(db.String(255))

    def to_dict(self):
        """安全的数据序列化方法，兼容所有使用场景"""
        return {
            # 直接映射字段，不改变原始数据
            'name': self.name,
            'type': self.type,
            'created_date': self.created_date,
            'play_count': self.play_count or 0,  # 空值处理
            'favorite_count': self.favorite_count or '0',  # 保留字符串类型
            'share_count': self.share_count or 0,
            'comment_count': self.comment_count or 0,
        }

    def safe_favorite_count(self):  # [!code ++]
        try:
            return int(self.favorite_count)
        except (ValueError, TypeError):
            return 0

    __table_args__ = (
        db.Index('idx_name', 'name'),          # 名称查询优化
        db.Index('idx_created', 'created_date'), # 时间排序优化
        db.Index('idx_type', 'type')           # 类型过滤优化
    )



# 用户收藏关系表（对应 user_collected_playlists）
class UserCollectedPlaylist(db.Model):
    __tablename__ = 'user_collected_playlists'
    id = db.Column(db.String(36), primary_key=True)
    username = db.Column(db.String(255))  # 根据实际用户表字段调整
    playlist_name = db.Column(db.String(255))  # 关联 data2.name
    created_at = db.Column(db.DateTime, default=datetime.utcnow)
